package com.hwua.dao.impl;

import com.hwua.dao.TeacherDao;
import com.hwua.entity.Teacher;
import com.hwua.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author CQY
 * 实现教师接口
 */
public class TeacherDaoImpl implements TeacherDao {
    @Override
    public int save(Teacher teacher) {
        String sql = "insert into teacher(teaid,teaname) values(?,?)";
        Object[] params = {teacher.getTeaId(), teacher.getTeaName()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int update(Teacher teacher) {
        String sql = "update teacher set teaname=? where teaid=?";
        Object[] params = {teacher.getTeaName(), teacher.getTeaId()};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public int delete(String id) {
        String sql = "delete from teacher where teaid=?";
        Object[] params = {id};
        int row = 0;
        try {
            row = DBHelper.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }

    @Override
    public Teacher findById(String id) {
        Connection conn = DBHelper.getConnection();
        Teacher teacher = null;
        String sql = "select * from teacher where teaid=?";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setString(1, id);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                teacher = new Teacher();
                teacher.setTeaId(rs.getString("teaid"));
                teacher.setTeaName(rs.getString("teaname"));

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return teacher;
    }


    public Teacher findByName(String name) {
        Connection conn = DBHelper.getConnection();
        Teacher teacher = null;
        String sql = "select * from teacher where teaname=?";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setString(1, name);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                teacher = new Teacher();
                teacher.setTeaId(rs.getString("teaid"));
                teacher.setTeaName(rs.getString("teaname"));

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return teacher;
    }

    @Override
    public List<Teacher> findAll() {
        Connection conn = DBHelper.getConnection();
        List<Teacher> list = new ArrayList<Teacher>();
        Teacher teacher = null;
        String sql = "select * from teacher order by teaid asc ";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                teacher = new Teacher();
                teacher.setTeaId(rs.getString("teaid"));
                teacher.setTeaName(rs.getString("teaname"));
                list.add(teacher);


            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Teacher> findSearch(String keyword) {
        Connection conn = DBHelper.getConnection();
        List<Teacher> list = new ArrayList<Teacher>();
        Teacher teacher = null;
        String sql = "select * from teacher where teaname like '%"+keyword+"%' order by teaid asc ";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                teacher = new Teacher();
                teacher.setTeaId(rs.getString("teaid"));
                teacher.setTeaName(rs.getString("teaname"));
                list.add(teacher);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public int deleteBatch(String[] id) {
        int row=0 ;
        Connection conn=DBHelper.getConnection();
        String sql = "delete from teacher where teaid=?";
        try {
            PreparedStatement pre=conn.prepareStatement(sql);
            for(int i=0;i<id.length;i++){
                pre.setString(1,id[i]);
                //添加参数到批处理
                pre.addBatch();
            }
            //执行批处理
          int [] rows= pre.executeBatch();
            //统计删除行数
            for (int i = 0; i <rows.length ; i++) {
                row+=rows[i];
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return row;
    }

    @Override
    public List<Map<Object, String>> findReport() {
        Connection conn = DBHelper.getConnection();
        List<Map<Object,String>> list = new ArrayList<Map<Object,String>>();
        Map map = null;
        String sql = "select t.teaid,t.teaname,count(*) num,max(score)max,min(score)min,round(avg(score),2) numAvg,sum(score)sum  from teacher t,course c,score a \n" +
                "where t.teaid=c.teaid AND\n" +
                "a.courseid=c.courseid group by t.teaid,t.teaname ORDER BY TEAID asc";
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                map= new HashMap();
                map.put(1,rs.getInt("teaid"));
                map.put(2,rs.getString("teaname"));
                map.put(3,rs.getInt("num"));
                map.put(4,rs.getInt("max"));
                map.put(5,rs.getInt("min"));
                map.put(6,rs.getFloat("numAvg"));
                map.put(7,rs.getInt("sum"));
                list.add(map);

            }
            rs.close();
            pre.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}
